﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BSF.Db;
using HuntingFishGame.Domain.BLL.WeChat;
using HuntingFishGame.Domain.Model.WeChat;

namespace HuntingFishGame.Domain.DAL.WeChat
{
    public class WechatFollowDal
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(DbConn pubConn, long ID)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("ID", ID);
                string strSql = "select count(1) from tb_WechatFollow where ID=@ID";
                return LibConvert.ObjToInt(pubConn.ExecuteScalar(strSql.ToString(), ps.ToParameters())) > 0;
            });
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long Add(DbConn pubConn, WechatFollowModel model)
        {
            return SqlHelper.Visit(ps =>
            {
                DateTime dtnow = DateTime.Now;
                string strSql = @"insert into tb_WechatFollow(AppId,OpenId,OauthScope,IsAuth,IsSubscribe,SubscribeTime,UnSubscribeTime,Sex,NickName,Province,City,Country,HeadImgUrl,UnionId,Privilege,CreateTime,UpdateTime,Updatecs,IsDeleted)
                                    values (@AppId,@OpenId,@OauthScope,@IsAuth,@IsSubscribe,@SubscribeTime,@UnSubscribeTime,@Sex,@NickName,@Province,@City,@Country,@HeadImgUrl,@UnionId,@Privilege,@CreateTime,@UpdateTime,@Updatecs,@IsDeleted);select @@IDENTITY";
                ps.Add("@AppId", model.AppId);
                ps.Add("@OpenId", model.OpenId);
                ps.Add("@OauthScope", model.OauthScope);
                ps.Add("@IsAuth", model.IsAuth);
                ps.Add("@IsSubscribe", model.IsSubscribe);
                ps.Add("@SubscribeTime", dtnow);
                ps.Add("@UnSubscribeTime", dtnow);
                ps.Add("@Sex", model.Sex);
                ps.Add("@NickName", model.NickName);
                ps.Add("@Province", model.Province);
                ps.Add("@City", model.City);
                ps.Add("@Country", model.Country);
                ps.Add("@HeadImgUrl", model.HeadImgUrl);
                ps.Add("@UnionId", model.UnionId);
                ps.Add("@Privilege", model.Privilege);
                ps.Add("@CreateTime", dtnow);
                ps.Add("@UpdateTime", dtnow);
                ps.Add("@Updatecs", 0);
                ps.Add("@IsDeleted", false);
                
                string id = pubConn.ExecuteScalar(strSql, ps.ToParameters()).ToString();
                return LibConvert.StrToInt64(id);
            });
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DbConn pubConn, WechatFollowModel model)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "update tb_WechatFollow set NickName=@NickName,HeadImgUrl=@HeadImgUrl,IsSubscribe=@IsSubscribe,Updatecs=@Updatecs,UpdateTime=@UpdateTime where ID=@ID";
                ps.Add("@NickName", model.NickName);
                ps.Add("@HeadImgUrl", model.HeadImgUrl);
                ps.Add("@IsSubscribe", model.IsSubscribe);
                ps.Add("@Updatecs", model.Updatecs);
                ps.Add("@UpdateTime", model.UpdateTime);
                ps.Add("@ID", model.Id);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool Delete(DbConn pubConn, long ID)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "delete from tb_WechatFollow where ID=@ID";
                ps.Add("@ID", ID);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="openId"></param>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<WechatFollowModel> GetList(DbConn pubConn, string openId, out int count, int pageIndex = 1, int pageSize = 10)
        {
            int _count = 0;
            List<WechatFollowModel> model = SqlHelper.Visit<List<WechatFollowModel>>(ps =>
            {
                string sqlwhere = "";
                StringBuilder sql = new StringBuilder();
                sql.Append("select ROW_NUMBER() over(order by T.ID desc) as rownum,T.* from tb_WechatFollow T where 1=1 ");
                if (!string.IsNullOrWhiteSpace(openId))
                {
                    ps.Add("OpenId", openId);
                    sqlwhere += " and ( T.OpenId =@OpenId )";
                }
                _count = Convert.ToInt32(pubConn.ExecuteScalar("select count(1) from tb_WechatFollow T where 1=1 " + sqlwhere, ps.ToParameters()));
                string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageSize * pageIndex;
                DataTable table = pubConn.SqlToDataTable(sqlSel, ps.ToParameters());
                List<WechatFollowModel> list = new List<WechatFollowModel>();
                foreach (DataRow dr in table.Rows)
                {
                    list.Add(WechatFollowModel.CreateModel(dr));
                }
                return list;
            });
            count = _count;
            return model;
        }

        /// <summary>获取详情</summary>
        /// <param name="pubConn"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public WechatFollowModel GetInfo(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("ID", id);
                string sql = "select * from tb_WechatFollow where ID=@ID";
                DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                if (table != null && table.Rows.Count > 0)
                {
                    return WechatFollowModel.CreateModel(table.Rows[0]);
                }
                else
                {
                    return null;
                }
            });
        }

        /// <summary>
        /// 获取详情
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="openId"></param>
        /// <returns></returns>
        public WechatFollowModel GetModelDetail(DbConn pubConn, string openId)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("OpenId", openId);
                string sql = "select top 1 * from tb_WechatFollow where OpenId=@OpenId ";
                
                DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                if (table != null && table.Rows.Count > 0)
                {
                    return WechatFollowModel.CreateModel(table.Rows[0]);
                }
                else
                {
                    return null;
                }
            });
        }
        
    }
}
